/**
 * 
 */
package com.base.myproject.client.busi.service.assessment;

import java.util.ArrayList;
import java.util.List;

import com.base.myproject.client.busi.BaseStore;
import com.base.myproject.client.busi.JDBCModelData;
import com.base.myproject.client.jdbc.StoreInfo;
import com.base.myproject.client.tools.Debug;
import com.base.myproject.client.ui.BaseGrid;
import com.base.myproject.client.ui.celleditor.CellEditorFactory;
import com.extjs.gxt.ui.client.Style.SelectionMode;
import com.extjs.gxt.ui.client.data.ModelType;
import com.extjs.gxt.ui.client.event.ButtonEvent;
import com.extjs.gxt.ui.client.event.SelectionListener;
import com.extjs.gxt.ui.client.widget.ContentPanel;
import com.extjs.gxt.ui.client.widget.HorizontalPanel;
import com.extjs.gxt.ui.client.widget.Html;
import com.extjs.gxt.ui.client.widget.MessageBox;
import com.extjs.gxt.ui.client.widget.VerticalPanel;
import com.extjs.gxt.ui.client.widget.button.Button;
import com.extjs.gxt.ui.client.widget.form.SimpleComboBox;
import com.extjs.gxt.ui.client.widget.grid.CheckBoxSelectionModel;
import com.extjs.gxt.ui.client.widget.grid.ColumnConfig;
import com.extjs.gxt.ui.client.widget.grid.ColumnModel;
import com.google.gwt.user.client.ui.Label;
import com.google.gwt.user.client.ui.Widget;

/**
 * 考核查询，只根据年月进行查询
 * @author Administrator
 *2010-1-23
 */
public class AssessmentQuery extends ContentPanel{
	BaseStore<JDBCModelData> store;
	
	CellEditorFactory celleditorfactory = new CellEditorFactory();
	SimpleComboBox<String> year;
	SimpleComboBox<String> month;
	public AssessmentQuery()
	{
		init();
		
	}
	void init() {
		year = celleditorfactory.getYEARCombobox();
		month = celleditorfactory.getMONTHCombobox();
		month.setAllowBlank(false);
		year.setAllowBlank(false);
		setWidth("760px");
		setHeading("员工绩效查询");
		initstore();
		
		initWidget();
	}
	void initstore() {
		ModelType type = new ModelType();
		type.setRoot("records");
		type.setRecordName("record");
		type.addField("bcode");

		type.addField("assesstotal");
		
		StoreInfo info = new StoreInfo();
		
		info.setTablename("notable");
		info.setNeedAllRow("no");
		info.setForcenolimit("true");
		
	
		info.setSql(initreloadparam());
	
		store = new BaseStore<JDBCModelData>(type, info,false);
	}
	void initWidget() {
		VerticalPanel vpanel = new VerticalPanel();
		
		vpanel.add(new Html("<br>"));
		vpanel.add(initQuery());
		vpanel.add(new Html("<br>"));
		vpanel.add(CreateGrid());
		
		add(vpanel);
	}
	
	Widget initQuery()
	{
		HorizontalPanel hpanel = new HorizontalPanel();
		hpanel.add(new Label("考核 年："));
		
		hpanel.add(year);
		
		
		hpanel.add(new Label("月："));
	
		hpanel.add(month);
		
		Button query = new Button("查询");
		hpanel.add(query);
		query.addSelectionListener(new SelectionListener<ButtonEvent>(){

			@Override
			public void componentSelected(ButtonEvent ce) {
				Debug.printf(year.getValue().getValue());
				Debug.printf(month.getSimpleValue());
				if(year.isValid() && month.isValid())
				{
				StoreInfo info  = store.getStoreinfo();;
				info.setSql(initreloadparam());
				store.setStoreinfo(info);
				store.reload();
				}
				else
				{
					MessageBox.alert("警告", "请先录入 年，月", null);
				}
			}
			
		});
		return hpanel;
	}
	Widget CreateGrid()
	{
		CheckBoxSelectionModel cbs = new CheckBoxSelectionModel();
		List<ColumnConfig> columns = new ArrayList<ColumnConfig>();
		columns.add(cbs.getColumn());
	
		columns.add(new ColumnConfig("bcode", "名字", 50));
		//columns.add(new ColumnConfig("email", "会员邮箱", 50));
		columns.add(new ColumnConfig("assesstotal", "提成", 100));
		
		
		
		ColumnModel cm = new ColumnModel(columns);
		cbs.setSelectionMode(SelectionMode.SINGLE);
		BaseGrid  grid = new BaseGrid(store, cm,false,false,null);
		grid.setSelectionModel(cbs);

		grid.setSize(760,300);
		grid.getView().setForceFit(true);
		return grid;
	}
	String initreloadparam()
	{
		java.lang.StringBuffer sbf = new StringBuffer();
		sbf.append(" select (select bname from bcode where bcode.bcode= z.bcode) as bcode,cast (sum(assessrate ) as money) as assesstotal");
		sbf.append("     ");
		sbf.append(" from");
		sbf.append(" (   ");
		sbf.append(" select a.bcode as bcode   ");
		sbf.append(" ,   ");
		sbf.append(" case");
		sbf.append(" when f.bcode is not null        ");
		sbf.append(" then");
		sbf.append(" 	case when f.methodse='实收比例'");
		sbf.append(" 	then             ");
		sbf.append(" 		case when method='指定'      ");
		sbf.append(" 			then f.method1 * realtotal ");
		sbf.append(" 		when method='轮牌'           ");
		sbf.append(" 			then f.method2 * realtotal ");
		sbf.append(" 		when method='开发'           ");
		sbf.append(" 			then f.method3 * realtotal ");
		sbf.append(" 	end");
		sbf.append(" 	when f.methodse='应收比例'     ");
		sbf.append(" 	then             ");
		sbf.append(" 		case when method='指定'      ");
		sbf.append(" 			then f.method1 * total     ");
		sbf.append(" 		when method='轮牌'           ");
		sbf.append(" 			then f.method2 * total     ");
		sbf.append(" 		when method='开发'           ");
		sbf.append(" 			then f.method3 * total     ");
		sbf.append(" 	end");
		sbf.append(" 	when f.methodse='固定金额'     ");
		sbf.append(" 	then             ");
		sbf.append(" 		case when method='指定'      ");
		sbf.append(" 			then f.method1             ");
		sbf.append(" 		when method='轮牌'           ");
		sbf.append(" 			then f.method2             ");
		sbf.append(" 		when method='开发'           ");
		sbf.append(" 			then f.method3             ");
		sbf.append(" 	end");
		sbf.append(" 	end");
		sbf.append(" when h.systemid is not null     ");
		sbf.append(" then");
		sbf.append(" 	case when h.methodse='实收比例'");
		sbf.append(" 	then             ");
		sbf.append(" 		case when method='指定'      ");
		sbf.append(" 			then h.method1 * realtotal ");
		sbf.append(" 		when method='轮牌'           ");
		sbf.append(" 			then h.method2 * realtotal ");
		sbf.append(" 		when method='开发'           ");
		sbf.append(" 			then h.method3 * realtotal ");
		sbf.append(" 	end");
		sbf.append(" 	when h.methodse='应收比例'     ");
		sbf.append(" 	then             ");
		sbf.append(" 		case when method='指定'      ");
		sbf.append(" 			then h.method1 * total     ");
		sbf.append(" 		when method='轮牌'           ");
		sbf.append(" 			then h.method2 * total     ");
		sbf.append(" 		when method='开发'           ");
		sbf.append(" 			then h.method3 * total     ");
		sbf.append(" 	end");
		sbf.append(" 	when h.methodse='固定金额'     ");
		sbf.append(" 	then             ");
		sbf.append(" 		case when method='指定'      ");
		sbf.append(" 			then h.method1             ");
		sbf.append(" 		when method='轮牌'           ");
		sbf.append(" 			then h.method2             ");
		sbf.append(" 		when method='开发'           ");
		sbf.append(" 			then h.method3             ");
		sbf.append(" 	end");
		sbf.append(" 	end");
		sbf.append(" when e.service is not null      ");
		sbf.append(" then");
		sbf.append(" 	case when e.methodse='实收比例'");
		sbf.append(" 	then             ");
		sbf.append(" 		case when method='指定'      ");
		sbf.append(" 			then e.method1 * realtotal ");
		sbf.append(" 		when method='轮牌'           ");
		sbf.append(" 			then e.method2 * realtotal ");
		sbf.append(" 		when method='开发'           ");
		sbf.append(" 			then e.method3 * realtotal ");
		sbf.append(" 	end");
		sbf.append(" 	when e.methodse='应收比例'     ");
		sbf.append(" 	then             ");
		sbf.append(" 		case when method='指定'      ");
		sbf.append(" 			then e.method1 * total     ");
		sbf.append(" 		when method='轮牌'           ");
		sbf.append(" 			then e.method2 * total     ");
		sbf.append(" 		when method='开发'           ");
		sbf.append(" 			then e.method3 * total     ");
		sbf.append(" 	end");
		sbf.append(" 	when e.methodse='固定金额'     ");
		sbf.append(" 	then             ");
		sbf.append(" 		case when method='指定'      ");
		sbf.append(" 			then e.method1             ");
		sbf.append(" 		when method='轮牌'           ");
		sbf.append(" 			then e.method2             ");
		sbf.append(" 		when method='开发'           ");
		sbf.append(" 			then e.method3             ");
		sbf.append(" 	end");
		sbf.append(" 	end");
		sbf.append(" when c.systemid is not null     ");
		sbf.append(" then");
		sbf.append(" 	case when c.methodse='实收比例'");
		sbf.append(" 	then             ");
		sbf.append(" 		case when method='指定'      ");
		sbf.append(" 			then c.method1 * realtotal ");
		sbf.append(" 		when method='轮牌'           ");
		sbf.append(" 			then c.method2 * realtotal ");
		sbf.append(" 		when method='开发'           ");
		sbf.append(" 			then c.method3 * realtotal ");
		sbf.append(" 	end");
		sbf.append(" 	when c.methodse='应收比例'     ");
		sbf.append(" 	then             ");
		sbf.append(" 		case when method='指定'      ");
		sbf.append(" 			then c.method1 * total     ");
		sbf.append(" 		when method='轮牌'           ");
		sbf.append(" 			then c.method2 * total     ");
		sbf.append(" 		when method='开发'           ");
		sbf.append(" 			then c.method3 * total     ");
		sbf.append(" 	end");
		sbf.append(" 	when c.methodse='固定金额'     ");
		sbf.append(" 	then             ");
		sbf.append(" 		case when method='指定'      ");
		sbf.append(" 			then c.method1             ");
		sbf.append(" 		when method='轮牌'           ");
		sbf.append(" 			then c.method2             ");
		sbf.append(" 		when method='开发'           ");
		sbf.append(" 			then c.method3             ");
		sbf.append(" 	end");
		sbf.append(" 	end");
		sbf.append("     ");
		sbf.append(" when g.systemid is not null     ");
		sbf.append(" then");
		sbf.append(" 	case when g.methodse='实收比例'");
		sbf.append(" 	then             ");
		sbf.append(" 		case when method='指定'      ");
		sbf.append(" 			then g.method1 * realtotal ");
		sbf.append(" 		when method='轮牌'           ");
		sbf.append(" 			then g.method2 * realtotal ");
		sbf.append(" 		when method='开发'           ");
		sbf.append(" 			then g.method3 * realtotal ");
		sbf.append(" 	end");
		sbf.append(" 	when g.methodse='应收比例'     ");
		sbf.append(" 	then             ");
		sbf.append(" 		case when method='指定'      ");
		sbf.append(" 			then g.method1 * total     ");
		sbf.append(" 		when method='轮牌'           ");
		sbf.append(" 			then g.method2 * total     ");
		sbf.append(" 		when method='开发'           ");
		sbf.append(" 			then g.method3 * total     ");
		sbf.append(" 	end");
		sbf.append(" 	when g.methodse='固定金额'     ");
		sbf.append(" 	then             ");
		sbf.append(" 		case when method='指定'      ");
		sbf.append(" 			then g.method1             ");
		sbf.append(" 		when method='轮牌'           ");
		sbf.append(" 			then g.method2             ");
		sbf.append(" 		when method='开发'           ");
		sbf.append(" 			then g.method3             ");
		sbf.append(" 	end");
		sbf.append(" 	end");
		sbf.append("     ");
		sbf.append("     ");
		sbf.append(" when d.systemid is not null     ");
		sbf.append(" then");
		sbf.append(" 	case when d.methodse='实收比例'");
		sbf.append(" 	then             ");
		sbf.append(" 		case when method='指定'      ");
		sbf.append(" 			then d.method1 * realtotal ");
		sbf.append(" 		when method='轮牌'           ");
		sbf.append(" 			then d.method2 * realtotal ");
		sbf.append(" 		when method='开发'           ");
		sbf.append(" 			then d.method3 * realtotal ");
		sbf.append(" 	end");
		sbf.append(" 	when d.methodse='应收比例'     ");
		sbf.append(" 	then             ");
		sbf.append(" 		case when method='指定'      ");
		sbf.append(" 			then d.method1 * total     ");
		sbf.append(" 		when method='轮牌'           ");
		sbf.append(" 			then d.method2 * total     ");
		sbf.append(" 		when method='开发'           ");
		sbf.append(" 			then d.method3 * total     ");
		sbf.append(" 	end");
		sbf.append(" 	when d.methodse='固定金额'     ");
		sbf.append(" 	then             ");
		sbf.append(" 		case when method='指定'      ");
		sbf.append(" 			then d.method1             ");
		sbf.append(" 		when method='轮牌'           ");
		sbf.append(" 			then d.method2             ");
		sbf.append(" 		when method='开发'           ");
		sbf.append(" 			then d.method3             ");
		sbf.append(" 	end");
		sbf.append(" 	end");
		sbf.append("     ");
		sbf.append("     ");
		sbf.append("     ");
		sbf.append(" end  as assessrate");
		sbf.append("     ");
		sbf.append("  from             ");
		sbf.append(" (   ");
		sbf.append(" select bcode,method,serviceitem,total, realtotal from       ");
		sbf.append(" (   ");
		sbf.append(" select bcode800 as bcode ,method800 as method,serviceitem,total,realtotal,upicode from s_service_po_g ");
		sbf.append(" 	union all        ");
		sbf.append("     ");
		sbf.append(" select bcode801 as bcode,method801  as method,serviceitem,total,realtotal,upicode from s_service_po_g ");
		sbf.append(" 	union all        ");
		sbf.append(" select bcode802 as bcode,method802 as method ,serviceitem,case when bcode810 is null  then total  when bcode810 ='' then total else total/2 end as total,case when bcode810 is null  then realtotal  when bcode810 ='' then realtotal else realtotal/2 end as realtotal,upicode from s_service_po_g");
		sbf.append(" 	union all ");
		sbf.append("            ");
		sbf.append(" select bcode810 as bcode,method810 as method ,serviceitem,case when bcode802 is null  then total  when bcode802 ='' then total else total/2 end as total,case when bcode802 is null  then realtotal  when bcode802 ='' then realtotal else realtotal/2 end as realtotal,upicode from s_service_po_g");
		sbf.append(" 	union all ");
		sbf.append(" select bcode900 as bcode,method900 as method,serviceitem,total,realtotal,upicode from s_service_po_g         ");
		sbf.append(" ) as a     ");
		sbf.append(" where  exists            ");
		sbf.append(" (          ");
		sbf.append("            ");
		sbf.append(" select * from s_service_po where datepart(year,serviceenddate) ="+year.getSimpleValue()+" and datepart(month,serviceenddate)="+month.getSimpleValue()+" and a.upicode = s_service_po.icode           ");
		sbf.append(" )          ");
		sbf.append(" and a.bcode is not null  ");
		sbf.append("            ");
		sbf.append(" ) as a     ");
		sbf.append("            ");
		sbf.append(" left join  ");
		sbf.append(" (          ");
		sbf.append(" select bcode,systemid,groupicode from bcode          ");
		sbf.append(" ) as b     ");
		sbf.append(" on a.bcode=b.bcode       ");
		sbf.append("            ");
		sbf.append(" left join  ");
		sbf.append(" (          ");
		sbf.append(" select systemid,bcode,service,max(method1) as method1,max(method2) as method2,   ");
		sbf.append(" max(method3) as method3,max(methodse) as methodse from assessment  group by bcode,systemid,service,methodse  ");
		sbf.append(" ) as f     ");
		sbf.append(" on a.serviceitem =f.service and  b.systemid = f.systemid and a.bcode = f.bcode   ");
		sbf.append("            ");
		sbf.append("            ");
		sbf.append(" left join  ");
		sbf.append(" (          ");
		sbf.append(" select bcode,systemid,max(method1) as method1,max(method2) as method2,           ");
		sbf.append(" max(method3) as method3,max(methodse) as methodse from assessment where service is null or service='' group by bcode,systemid,methodse   ");
		sbf.append(" ) as c     ");
		sbf.append(" on a.bcode=c.bcode and b.systemid = c.systemid       ");
		sbf.append("            ");
		sbf.append("            ");
		sbf.append(" left join  ");
		sbf.append(" (          ");
		sbf.append(" select systemid, service,max(method1) as method1,max(method2) as method2,        ");
		sbf.append(" max(method3) as method3,max(methodse) as methodse from assessment where bcode is null or bcode='' group by service,systemid,methodse     ");
		sbf.append(" ) as e     ");
		sbf.append(" on a.serviceitem =e.service and  b.systemid = e.systemid           ");
		sbf.append("            ");
		sbf.append(" left join  ");
		sbf.append(" (          ");
		sbf.append(" select systemid,max(method1) as method1,max(method2) as method2,   ");
		sbf.append(" max(method3) as method3,max(methodse) as methodse from assessment where (bcode is null or bcode='') and (service is null or service='') group by systemid,methodse   ");
		sbf.append(" ) as d     ");
		sbf.append(" on b.systemid=d.systemid ");
		sbf.append("            ");
		sbf.append("            ");
		sbf.append(" left join  ");
		sbf.append(" (          ");
		sbf.append(" select systemid,groupicode,max(method1) as method1,max(method2) as method2,      ");
		sbf.append(" max(method3) as method3,max(methodse) as methodse from assessment where (bcode is null or bcode='') and (service is null or service='') group by systemid,groupicode,methodse      ");
		sbf.append(" ) as g     ");
		sbf.append(" on b.systemid=g.systemid and b.groupicode = g.groupicode           ");
		sbf.append("            ");
		sbf.append(" left join  ");
		sbf.append(" (          ");
		sbf.append(" select systemid,groupicode,service,max(method1) as method1,max(method2) as method2,            ");
		sbf.append(" max(method3) as method3,max(methodse) as methodse from assessment where (bcode is null or bcode='') group by systemid,groupicode,service,methodse      ");
		sbf.append(" ) as h     ");
		sbf.append(" on b.systemid=h.systemid and b.groupicode = h.groupicode and a.serviceitem = h.service         ");
		sbf.append("  union all select bcode, cast (paid* ( select top 1 method from assessment_rule2g where a.paid/area >1 order by a.paid/area asc ) as money) as assessrate from ( select bcode ,sum(paid) paid from s_service_po where exists ( select * from assessment_bcode_tbl_g where assessment_bcode_tbl_g.bcode = s_service_po.bcode and methodicode='按照区间绩效' ) and datepart(year,serviceenddate) ="+year.getSimpleValue()+" and datepart(month,serviceenddate)="+month.getSimpleValue()+" group by bcode  ) as a  ");
		sbf.append(" ) as z     ");
		sbf.append("            ");
		sbf.append(" group by bcode           ");
		
		Debug.printf(sbf+"");
		return new String(sbf);
	}
}
